/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Bean;

import Objects.Album;
import Objects.Comment;
import Objects.Image;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.ejb.Stateless;

/**
 *
 * @author tinvukhac
 */
@Stateless(mappedName = "myPhotoBean")
public class myPhotoBean implements myPhotoBeanRemote {

    String connectionString = "jdbc:sqlserver://NTFSBK-PC\\SQLEXPRESS_R2;"
            + "databaseName=imageSite";
    String serverUserName = "sa";
    String serverPasswd = "123456";

    @Override
    public List<Album> getAlbumListByUserId(int userId) {
        List<Album> albumList = new ArrayList<Album>();
        Album tempAlbum;

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Albums WHERE userId=?");
            pstm.setString(1, Integer.toString(userId));
            ResultSet rs = pstm.executeQuery();

            while (rs.next()) {
                tempAlbum = new Album(rs.getInt("albumId"), rs.getString("albumName"),
                        rs.getInt("userId"), rs.getDate("dateCreate").toString(), rs.getString("thumbnail"));
                albumList.add(tempAlbum);
            }
            pstm.close();
        } catch (Exception e) {
            //throw e;
        }
        return albumList;
    }

    @Override
    public List<Image> getImageListByAlbumId(String albumId) {
        List<Image> imageList = new ArrayList<Image>();
        Image tempImage;

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Images WHERE albumId=?");
            pstm.setString(1, albumId);
            ResultSet rs = pstm.executeQuery();

            while (rs.next()) {
                tempImage = new Image(rs.getInt("imageId"), rs.getString("imageName"),
                        rs.getInt("albumId"), rs.getInt("views"),
                        rs.getString("tag"), rs.getString("link"));
                imageList.add(tempImage);
            }
            pstm.close();
        } catch (Exception e) {
            //throw e;
        }
        return imageList;
    }

    @Override
    public List<Comment> getCommentListByImageId(String imageId) {
        List<Comment> commentList = new ArrayList<Comment>();
        Comment tempComment;

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Comments WHERE imageId=?");
            pstm.setString(1, imageId);
            ResultSet rs = pstm.executeQuery();

            while (rs.next()) {
                tempComment = new Comment(rs.getInt("commentId"), rs.getInt("imageId"),
                        rs.getInt("userId"), rs.getString("contents"),
                        rs.getDate("time").toString());
                commentList.add(tempComment);
            }
            pstm.close();
        } catch (Exception e) {
            //throw e;
        }
        return commentList;
    }

    @Override
    public String getAlbumNameByAlbumId(String albumId) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT albumName FROM Albums WHERE albumId=?");
            pstm.setString(1, albumId);
            ResultSet rs = pstm.executeQuery();

            while (rs.next()) {
                return rs.getString("albumName");
            }
            pstm.close();
        } catch (Exception e) {
            //throw e;
        }
        return "Album Name";
    }

    @Override
    public void insertNewComment(String imageId, String userId, String contents, String date) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("INSERT INTO Comments (imageId, userId, contents, time) VALUES (?, ?, ?, ?)");
            pstm.setString(1, imageId);
            pstm.setString(2, userId);
            pstm.setString(3, contents);
            pstm.setString(4, date);
            pstm.executeUpdate();
            pstm.close();
        } catch (Exception e) {
            //throw e;
        }
    }

    @Override
    public void insertNewReport(String commentId, String userId) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("INSERT INTO Reports (commentId, userId) VALUES (?, ?);UPDATE Users SET currentPoint = currentPoint + 1 WHERE userId IN (SELECT userId FROM Comments WHERE Comments.commentId = ?)");
            pstm.setString(1, commentId);
            pstm.setString(2, userId);
            pstm.setString(3, commentId);
            pstm.executeUpdate();
            pstm.close();
        } catch (Exception e) {
            //throw e;
        }
    }

    @Override
    public boolean checkCommentWasDislikeByUser(String commentId, String userId) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Reports WHERE commentId=? AND userId=?");
            pstm.setString(1, commentId);
            pstm.setString(2, userId);
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                pstm.close();
                return true;
            }
        } catch (Exception e) {
            //throw e;
        }
        return false;
    }

    @Override
    public int getNumberOfImagesInAlbum(int albumId, int userId) throws Exception {
        int number = 1;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT count(*)as number FROM Albums,Users,Images WHERE Albums.userId=Users.userId AND Images.albumId=Albums.albumId AND Users.userId=? AND Albums.albumId=?");
            pstm.setInt(1, userId);
            pstm.setInt(2, albumId);
            ResultSet rs = pstm.executeQuery();

            rs.next();
            number = Integer.parseInt(rs.getString("number"));
            pstm.close();
            return number;
        } catch (Exception e) {
            e.printStackTrace();
            return number;
        }
    }

    @Override
    public Album getAlbumFromAlbumId(int albumId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("select * from Albums where albumId=?");
            pstm.setInt(1, albumId);
            ResultSet rs = pstm.executeQuery();
            
            if(rs.next()==false)
                return  null;
            String albumName=rs.getString("albumName");
            int userId=rs.getInt("userId");
            String dateCreate=rs.getString("dateCreate");
            String thumbnail=rs.getString("thumbnail");
            
            pstm.close();
            return new Album(albumId, albumName, userId, dateCreate, thumbnail);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
